#!/usr/bin/perl -w
#use lib qw(/root/PARSERS);
#require StatDB;
use DBI;
use Encode;
#use Digest::MD5 qw(md5_hex);
use String::CRC32;
use strict;
use utf8;

no warnings;

#my $dbh=StatDB->connect_stat(); # DB stat - localhost
#my $dbh=StatDB->connect_calls();# DB calls - 10.77.30.138
#my $dbh=StatDB->connect_mtt(); # DB stat - localhost
#my $dbh=StatDB->connect_temp_stat(); # DB stat - localhost

my $dir = '../data/';

my $dbh = DBI->connect(
        'dbi:mysql:database=mtt',
        'root',
		'a1s3an'
) || die DBI->errstr;

$dbh->do('SET NAMES utf8');

$dbh->do('DROP TABLE IF EXISTS masks');


$dbh->do('
	CREATE TABLE IF NOT EXISTS masks(
		`mask` INT UNSIGNED PRIMARY KEY,
		`country_code` INT UNSIGNED,
		`code1` INT UNSIGNED DEFAULT NULL,
		`code2` INT UNSIGNED DEFAULT NULL,
		`fake_mask` varchar(3) DEFAULT NULL,
		`country` varchar(40) NOT NULL,
		`area` varchar (42) NOT NULL,
		`region` varchar(42) NOT NULL,
		`city` varchar(20) NOT NULL,
		`operator` varchar(50) NOT NULL,
		`type` char(1) NOT NULL,
		`crc`  varchar(10) NOT NULL,
		KEY `mask`(`mask`),
		KEY `crc`(`crc`),
		KEY `country_code`(`country_code`),
		KEY `code1`(`code1`),
		KEY `code2`(`code2`),
		KEY `country`(`country`),
		KEY `city`(`city`),
		KEY `operator`(`operator`),
		KEY `area`(`area`),
		KEY `region`(`region`)
	)ENGINE=MyISAM DEFAULT CHARSET=utf8'
) || die $dbh->errstr;





my $outfile = 'masks_load';
open(OUT, '>:encoding(UTF-8)', $outfile) || die $!;

print OUT "7\t7\tNULL\tNULL\tfr\tРоссия\t\t\t\t\tf\t\n";
print OUT "7800\t7\t800\tNULL\tfr\tРоссия\t\t\t\t\tf\t999999999\n";

my %masks = ();

open(F, '<:encoding(UTF-8)', "${dir}DEF_national.csv") || die $!;
while (<F>) {
	next unless /[\w]/;
	s/"//g;
	my @row = split /\t/;

	my ($country, $code1, $code2, $region, $area, $city, $operator, $type) = 
			('Россия',$row[5], $row[6], $row[1], $row[7], $row[0], $row[4], 'm');

	foreach ($country, $code1, $code2, $area, $region, $city, $operator, $type)	{
		s/(?:^\s+|\s+$)//g;
	}

	next unless $code1 =~ /^\d+$/;

	my $country_code = '7';

	$country =~ s/\s+Моб\.?\s*$//i;

	my $masks = split_code( $code2 );
	foreach my $c2 ( @$masks ) {
		next if $masks{ $country_code . $code1 . $c2 };
		$masks{ $country_code . $code1 . $c2 } = [ $country_code, $country, $area, $region, $city, $operator, $code1, $c2, $type ];
	}

}
close F;

foreach my $mask ( keys %masks ) {
	my ($country_code, $country, $area, $region, $city, $operator, $code1, $code2, $type) = @{ $masks{$mask} };
	my $crc = crc32("$country$code1$operator$city");

	if ($code2 == 0) 
	    {
	    $mask =~ s/$code2$//;
	    $code2 = "NULL";
	    }
	if ($code1 == 0) 
	    {
	    $mask =~ s/$code1$//;
	    $code1 = "NULL";
	    }
	
	print OUT "$mask\t$country_code\t$code1\t$code2\tmr\t$country\t$area\t$region\t$city\t$operator\t$type\t$crc\n";
}

%masks = ();
open(F, '<:encoding(UTF-8)', "${dir}ABC_national.csv") || die $!;
while (<F>) {
	next unless /[\w]/;
	s/"//g;

	my @row = split /\t/;

	my ($country_code, $country, $region, $city, $code1, $type) = (7, 'Россия', $row[2], $row[3], $row[4], 'f');
	

	foreach ($country, $code1, $region)	{
		s/(?:^\s+|\s+$)//g;
	}

#	next unless $code1 =~ /^\d+$/;
	$country =~ s/\s+Моб\.?\s*$//i;
		
	if ( $code1 == 495 || $code1 == 499 || $code1 == 812 ) {
		foreach (1..9) {
			$masks{ $country_code . $code1 . $_ } = [ $country_code, $country, $region, $city, $code1, $_, $type ];
		}
	}
	else {
		my $code2 = 'NULL';
		if ( $code1 =~ /^\s*(\d{3})\s*\(([^)]+)/ ) {
			($code1, $code2) = ($1, $2);
			my $masks = split_code($code2);
			foreach my $c2 ( @$masks ) {
				next if $masks{ $country_code . $code1 . $c2 };
				$masks{ $country_code . $code1 . $c2 } = [ $country_code, $country, $region, $city, $code1, $c2, $type ];
			}
		}
		elsif ( $code1 =~ /^\s*(\d{3})(\d+)/ ) {
			($code1, $code2) = ($1, $2);
			next if $masks{ $country_code . $code1 . $code2 };
			$masks{ $country_code . $code1 . $code2 } = [ $country_code, $country, $region, $city, $code1, $code2, $type ];
		}
		else {
			next if $masks{ $country_code . $code1 };
			$masks{ $country_code . $code1 } = [ $country_code, $country, $region, $city, $code1, 'NULL', $type ];
		}
	}
}
close F;

foreach my $mask ( keys %masks ) {
	my ($country_code, $country, $region, $city, $code1, $code2, $type) = @{ $masks{$mask} };
	my $crc = crc32("$country$code1$code2$city");


	if ($code2 == 0) 
	    {
	    $mask =~ s/$code2$//;
	    $code2 = "NULL";
	    }
	if ($code1 == 0) 
	    {
	    $mask =~ s/$code1$//;
	    $code1 = "NULL";
	    }


	print OUT "$mask\t$country_code\t$code1\t$code2\tfr\t$country\t\t$region\t$city\t\t$type\t$crc\n";
}

%masks = ();
my ($s_store, $s_country) = ('', '');
open(F, '<:encoding(UTF-8)', "${dir}ABC_international.csv") || die $!;
while (<F>) {

	s/"//g;
	next unless /\w/;
	my @row = split /\t/;

	my ($country_code, $country, $code1, $code2, $area, $region, $city, $operator, $type);

	($country_code, $country, $code1, $code2, $area, $type) = ($row[3], $row[0], $row[4], $row[5], $row[2], 'f');
#	($area, $region) = $row[2] =~ /^\s*([^,]+),?\s*(.*)\s*$/;

	if ( $country_code ) {
		$s_store = $country_code;
	}
	else {
		$country_code = $s_store;
	}

	if ( $country ) {
		$s_country = $country;
	}
	else {
		$country = $s_country;
	}
	
	foreach ($country, $code1, $code2, $area, $region, $city, $operator, $type)	{
		s/(?:^\s+|\s+$)//g;
	}

	next unless $country_code =~ /^\d+$/;

	$country =~ s/\s+Моб\.?\s*$//i;
	my $fake = 'mn';

	if ( $code2 =~ /\d/ ) {
		$type = 'm';
		$code2 =~ s/[^\d,()-]//g;

		my $masks = split_code($code2);
		foreach my $c1 ( @$masks ) {
			next if $masks{ $country_code . $c1 };
			$masks{ $country_code . $c1 } = [ $country_code, $c1, $country, $type, '' ];
		}
	}
#	elsif ( $code1 =~ /[(,-]/ ) {
#		$masks = split_code($code1);
#		foreach my $c1 ( @$masks ) {
#			next if $masks{ $country_code . $c1 }++;
#			print OUT "$country_code$c1\t$country_code\t\t\t\t$country\t$area\t$region\t$city\t$operator\t$type\n";
#		}
#	}
	elsif ( $country_code =~ /[(-]/ ) {
		my $masks = split_code($country_code);
		($country_code) = $country_code =~ /^\s*(\d+)/;

		foreach my $c1 ( @$masks ) {
			next if $masks{ $country_code . $c1 };
			$masks{ $country_code . $c1 } = [ $country_code, $c1, $country, 'f', '' ];
#			print OUT "$c1\t$country_code\t\t\t\t$country\t$area\t$region\t$city\t$operator\t$type\n";
		}
	}
	else {
		my $masks = split_code($code1);
		if ( $country =~ /США/i || $country =~ /Канада/i 
				|| $country =~ /Казахстан/i || $country =~ /Грузия/i ) {
			foreach my $c1 ( @$masks ) {
				next if $masks{ $country_code . $c1 };
				$masks{ $country_code . $c1 } = [ $country_code, $c1, $country, 'f', $area ];
			}
		}
		else {
			foreach my $c1 ( @$masks ) {
				if ( $masks{$country_code . $c1} ) {
					$masks{$country_code . $c1}->[-1] .= ', ' . $area;
				}
				else {
					$masks{ $country_code . $c1 } = [ $country_code, $c1, $country, 'f', $area ];
				}
			}
		}
		
#		print OUT "$country_code$code1\t$country_code\t\t\t\t$country\t$area\t$region\t$city\t$operator\t$type\n";
	}

}
close F;

foreach my $mask ( keys %masks ) {
	my ($country_code, $c1, $country, $type, $area) = @{ $masks{$mask} };  #ADD OPERATOR
	my $crc = crc32("$country_code${country}${area}");

######################### ALL MN ##########################################################
#####	print OUT "$mask\t$country_code\t$c1\t\t$fake\t$country\t\t\t\t\t$type\t$crc\n";
	print OUT "$mask\t$country_code\t$c1\t\tmn\t$country\t$area\t\t\t$type\t$crc\n";
}

close OUT;

my $res = `mysql -u root -pa1s3an -D mtt -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks "`;


#my $res = `mysql -u root -p123 -D stat -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks"`;
#my $res = `mysql -ulexa -pcallsmtt -Dcalls -h 10.77.30.138 -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks"`;
#my $res = `mysql -ulexa -pcallsmtt -Dtemp_stat -h 10.77.30.136 -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks"`;
#my $res = `mysql -u root -p123 -D mtt -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks"`;

$dbh->do('UPDATE masks SET code2 = NULL where code2 = 0');
$dbh->do('UPDATE masks SET code1 = NULL where code1 = 0');



sub split_code {
	my ($str) = @_;
	my $res = [];

	my ($prefix, $c1) = ('', '');
	if ( $str =~ /\d+\s*\(/ ) {
		($prefix, $c1) = $str =~ /^(\d+)\s*\(\s*([^)]+)/;
		($c1, $prefix) = ($prefix, '') if $c1 =~ /[^\d,\s-]/;
	}
	else {
		$c1 = $str;
	}
	foreach my $p ( split /\s*,\s*/, $c1 ) {
		my ($c2, $c3) = split /\s*\-\s*/, $p;
		if ($c3) {
			($c2, $c3) = ( int($c2), int($c3) );
			my $l = length $c3;
			while ($c2 <= $c3) {
				push @$res, sprintf("%s%0${l}s", $prefix, $c2++);
			}
		}
		else {
			push @$res, $prefix . $c2;
		}
	}

	return $res;
}
